# 一个方法里面可以执行增删改查
# 此方法在子查询中不能使用
import pymysql


def mysql_operation(op_type, sql):
    db = None
    try:
        db = pymysql.connect(
            host='localhost',
            port=3307,
            user='root',
            password='123456',
            database='myschools',
            charset='utf8'
        )
        cursor = db.cursor()

        if op_type == 'select':
            cursor.execute(sql)
            # 判断是否需要返回多条还是单条数据
            if 'limit 1' in sql.lower() or 'fetchone' in sql.lower():
                result = cursor.fetchone()
            else:
                result = cursor.fetchall()
            return result
        else:
            # 执行增删改操作
            cursor.execute(sql)
            db.commit()
            return "操作成功"

    except Exception as e:
        if db:
            db.rollback()
        return f"异常: {e}"
    finally:
        if db:
            db.close()


# 查询
# select_all_sql = "select * from students"
# print(mysql_operation('select', select_all_sql))
# # 添加
# insert_sql = "INSERT INTO students VALUES(10,'张三','男',25,'1999-11-27',17630285209,2,2)"
# print(mysql_operation('insert', insert_sql))
# # 修改
# update_sql = "update students set age=20 where id=1"
# print(mysql_operation('update', update_sql))
# 删除
delete_sql = "delete from students where id=10"
print(mysql_operation('delete', delete_sql))
